﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;

namespace OgilvyOne.SqlServerDAL
{
    public class IndexBannerDal : IIndexBanner
    {
        #region IIndexBanner 成员
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        public int Add(IndexBannerModel model)
        {
            string str = @"declare @num int;select @num=max(ORDER_NUM)from COFCO_INDEX_BANNER;";
            str += "insert into COFCO_INDEX_BANNER ([BANNER_IMG],[BANNER_LINK],[BANNER_DATE],[STATE],BANNER_NAME,ORDER_NUM,ADDUSER,ADDTIME)values(@BANNER_IMG,@BANNER_LINK,@BANNER_DATE,@STATE,@BANNER_NAME,case when @num is null then 1 else @num+1 end ,@ADDUSER,@ADDTIME)";
            SqlParameter[] parameters = {					
					new SqlParameter("@BANNER_IMG",SqlDbType.VarChar,50),
					new SqlParameter("@BANNER_LINK", SqlDbType.VarChar,200),
					new SqlParameter("@BANNER_DATE", SqlDbType.VarChar,50),
	                new SqlParameter("@STATE", SqlDbType.Int),	
                    new SqlParameter("@BANNER_NAME", SqlDbType.VarChar,100),	
                    new SqlParameter("@ADDUSER", SqlDbType.VarChar,50),	
                    new SqlParameter("@ADDTIME", SqlDbType.DateTime)};
            parameters[0].Value = model.Image;
            parameters[1].Value = model.Link;
            if (model.Date == null)
            {
                parameters[2].Value = DateTime.Now.ToShortDateString();
            }
            else
              parameters[2].Value = model.Date;
            parameters[3].Value = model.State;
            parameters[4].Value = model.BannerName;
            parameters[5].Value = model.Adduser;
            parameters[6].Value = model.Addtime;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, parameters);
        }

        public bool Update(IndexBannerModel model)
        {
            string str = "UPDATE COFCO_INDEX_BANNER SET BANNER_IMG=@BANNER_IMG,BANNER_LINK=@BANNER_LINK,BANNER_DATE=@BANNER_DATE,STATE=@STATE,BANNER_NAME=@BANNER_NAME,ORDER_NUM=@ORDER_NUM,ADDUSER=@ADDUSER,ADDTIME=@ADDTIME where BANNER_ID=@BANNER_ID";
            SqlParameter[] parameters = {
					new SqlParameter("@BANNER_IMG",SqlDbType.VarChar,50),
					new SqlParameter("@BANNER_LINK", SqlDbType.VarChar,200),
					new SqlParameter("@BANNER_DATE", SqlDbType.VarChar,50),
	              new SqlParameter("@STATE", SqlDbType.Int),	
                  new SqlParameter("@BANNER_NAME", SqlDbType.VarChar,100),
	               new SqlParameter("@ORDER_NUM", SqlDbType.Int),	
                  new SqlParameter("@ADDUSER", SqlDbType.VarChar,50),	
                  new SqlParameter("@ADDTIME", SqlDbType.DateTime),
                  new SqlParameter("@BANNER_ID", SqlDbType.Int)};
            parameters[0].Value = model.Image;
            parameters[1].Value = model.Link;
            if (model.Date == null)
            {
                parameters[2].Value = DateTime.Now.ToShortDateString();
            }
            else
            {
                parameters[2].Value = model.Date;
            }
            parameters[3].Value = model.State;
            parameters[4].Value = model.BannerName;
            parameters[5].Value = model.OrderNum;
            parameters[6].Value = model.Adduser;
            parameters[7].Value = model.Addtime;
            parameters[8].Value = model.Id;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, parameters) > 0;
        }

        public bool Delete(int Banner_ID)
        {
            string str = "DELETE FROM [cofco].[dbo].[COFCO_INDEX_BANNER] where BANNER_ID=" + Banner_ID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, str, null) > 0;
        }

        public bool DeleteList(string Banner_IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_INDEX_BANNER ");
            strSql.Append(" where BANNER_ID in (" + Banner_IDlist + ")  ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }

        public IndexBannerModel GetModel(int Banner_ID)
        {
            string str = "select * from COFCO_INDEX_BANNER where BANNER_ID=" + Banner_ID;
            IndexBannerModel model = new IndexBannerModel();
            DataSet ds = SQLHelper.Query(str);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["BANNER_ID"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["BANNER_ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ORDER_NUM"].ToString() != "")
                {
                    model.OrderNum = int.Parse(ds.Tables[0].Rows[0]["ORDER_NUM"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BANNER_IMG"].ToString() != "")
                {
                    model.Image =ds.Tables[0].Rows[0]["BANNER_IMG"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BANNER_LINK"].ToString() != "")
                {
                    model.Link = ds.Tables[0].Rows[0]["BANNER_LINK"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BANNER_DATE"].ToString() != "")
                {
                    model.Date = ds.Tables[0].Rows[0]["BANNER_DATE"].ToString();
                }
                if (ds.Tables[0].Rows[0]["STATE"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["STATE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BANNER_NAME"].ToString() != "")
                {
                    model.BannerName = ds.Tables[0].Rows[0]["BANNER_NAME"].ToString();
                }
                 if (ds.Tables[0].Rows[0]["ADDUSER"].ToString() != "")
                {
                    model.Adduser = ds.Tables[0].Rows[0]["ADDUSER"].ToString();
                }
                 if (ds.Tables[0].Rows[0]["ADDTIME"].ToString() != "")
                {
                    model.Addtime = Convert.ToDateTime(ds.Tables[0].Rows[0]["ADDTIME"].ToString());
                }
                       return model;
            }
            else
            {
                return null;
            }           
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM COFCO_INDEX_BANNER ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by ORDER_NUM asc");
            return SQLHelper.Query(strSql.ToString());
        }
        public bool Exists(string bannernme, int bannerid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from COFCO_INDEX_BANNER");
            strSql.Append(" where BANNER_NAME=@BANNER_NAME and BANNER_ID not in (" + bannerid + ")");
            SqlParameter[] parameters = {
					new SqlParameter("@BANNER_NAME", SqlDbType.VarChar,100)
                    };
            parameters[0].Value = bannernme;

            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, strSql.ToString(), parameters).ToString()) > 0;
        }
        public IndexBannerModel GetModelByOrderNum(int OrderNum)
        {
            string str = "select * from COFCO_INDEX_BANNER where ORDER_NUM=" + OrderNum;
            IndexBannerModel model = new IndexBannerModel();
            DataSet ds = SQLHelper.Query(str);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["BANNER_ID"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["BANNER_ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ORDER_NUM"].ToString() != "")
                {
                    model.OrderNum = int.Parse(ds.Tables[0].Rows[0]["ORDER_NUM"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BANNER_IMG"].ToString() != "")
                {
                    model.Image = ds.Tables[0].Rows[0]["BANNER_IMG"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BANNER_LINK"].ToString() != "")
                {
                    model.Link = ds.Tables[0].Rows[0]["BANNER_LINK"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BANNER_DATE"].ToString() != "")
                {
                    model.Date = ds.Tables[0].Rows[0]["BANNER_DATE"].ToString();
                }
                if (ds.Tables[0].Rows[0]["STATE"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["STATE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BANNER_NAME"].ToString() != "")
                {
                    model.BannerName = ds.Tables[0].Rows[0]["BANNER_NAME"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ADDUSER"].ToString() != "")
                {
                    model.Adduser = ds.Tables[0].Rows[0]["ADDUSER"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ADDTIME"].ToString() != "")
                {
                    model.Addtime = Convert.ToDateTime(ds.Tables[0].Rows[0]["ADDTIME"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            } 
        }
        #endregion
    }
}
